package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.swing.table.DefaultTableModel;

import util.Util;
import model.Course;
import model.SC;
import model.Student;

public class SCDao {
	public List <SC> findAll(){
		List <SC> scs = new ArrayList<SC>();
		String sql1 = "select stu.id as stuId ,name,age,sex,cour.id as courId,cname,period ";
		String sql2 = "from db_student_p as stu,db_course_p as cour,db_stu_cour as sc ";
		String sql3 = "where stu.id=sc.sid and cour.id=sc.cid";
		Connection con = null;
		try{
			con = Util.getConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(sql1 + sql2 + sql3);
			while(rs.next()){
				Student stu = new Student(rs.getInt("stuId"),rs.getString("name"),rs.getInt("age"),rs.getString("sex"));
				Course cour = new Course(rs.getInt("courId"),rs.getString("cname"),rs.getInt("period"));
				SC sc = new SC(stu , cour);
				scs.add(sc);
			}
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			if(con != null)
				try {
					Util.closeConnection(con);
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return scs;
	}
	public List <Course> findAllCourse(){
		List <Course> cours = new ArrayList<Course>();
		String sql1 = "select distinct cour.id,cname,period ";
		String sql2 = "from db_student_p as stu,db_course_p as cour,db_stu_cour as sc ";
		String sql3 = "where stu.id=sc.sid and cour.id=sc.cid";
		Connection con = null;
		try{
			con = Util.getConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(sql1 + sql2 + sql3);
			while(rs.next()){
				Course cour = new Course(rs.getInt("id"),rs.getString("cname"),rs.getInt("period"));
				cours.add(cour);
			}
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			if(con != null)
				try {
					Util.closeConnection(con);
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return cours;
	}
	public List <Student> findStuByCid(int id){
		List <Student> stus = new ArrayList<Student>();
		String sql1 = "select stu.id,name,age,sex ";
		String sql2 = "from db_student_p as stu,db_stu_cour as sc ";
		String sql3 = "where stu.id=sc.sid and sc.cid="+id;
		Connection con = null;
		try{
			con = Util.getConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(sql1 + sql2 + sql3);
			while(rs.next()){
				Student stu = new Student(rs.getInt("id"),rs.getString("name"),rs.getInt("age"),rs.getString("sex"));
				stus.add(stu);
			}
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			if(con != null)
				try {
					Util.closeConnection(con);
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return stus;
	}
	public List <Course> findSelCourByStuId(int id){
		List <Course> cours = new ArrayList<Course>();
		String sql1 = "select distinct cour.id,cname,period ";
		String sql2 = "from db_course_p as cour,db_stu_cour ";
		String sql3 = "where id=cid and sid="+id;
		Connection con = null;
		try{
			con = Util.getConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(sql1 + sql2 + sql3);
			while(rs.next()){
				Course cour = new Course(rs.getInt("id"),rs.getString("cname"),rs.getInt("period"));
				cours.add(cour);
			}
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			if(con != null)
				try {
					Util.closeConnection(con);
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return cours;
	}
	public boolean SelCour(int stuId , int courId){
		Connection con = null;
		try{
			con = Util.getConnection();
			String sql = "insert into db_stu_cour values (?,?)";
			PreparedStatement pst = con.prepareStatement(sql);
			pst.setInt(1 , stuId);
			pst.setInt(2, courId);
			pst.execute();
			return true;
		}
		catch(Exception e){
			e.printStackTrace();
			return false;
		}
		finally{
			if(con != null)
				try {
					Util.closeConnection(con);
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
	}
	public boolean DelSelCour(int stuId , int courId){
		Connection con = null;
		try{
			con = Util.getConnection();
			String sql = "delete from db_stu_cour where sid=? and cid=?";
			PreparedStatement pst = con.prepareStatement(sql);
			pst.setInt(1 , stuId);
			pst.setInt(2, courId);
			pst.execute();
			return true;
		}
		catch(Exception e){
			e.printStackTrace();
			return false;
		}
		finally{
			if(con != null)
				try {
					Util.closeConnection(con);
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
	}
}
}